/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package data_bases.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.faces.context.FacesContext;
import login.bean.LoginBean;

/**
 *
 * @author chucho
 */
public class DatabaseDAO {
    
    Connection connection;
    private LoginBean lb ;
    
    public DatabaseDAO() {
        lb = (LoginBean) FacesContext.getCurrentInstance()
                .getExternalContext().getSessionMap().get("loginBean");
        this.connection = lb.getCurrentConnection();
    }
    
    public void createDatabase(String dataBase) throws SQLException{
        Statement statement = connection.createStatement();
        //CREATE DATABASE db_name CHARACTER charset_name SET COLLATE collation_name
        String sql = "CREATE DATABASE "+dataBase;
        statement.executeUpdate(sql);
    }
    public void createDatabase(String dataBase, String characterSet) throws SQLException{
        Statement statement = connection.createStatement();
        //CREATE DATABASE db_name CHARACTER charset_name SET COLLATE collation_name
        String sql = "CREATE DATABASE "+dataBase+" DEFAULT CHARACTER SET "+characterSet;
        
        statement.execute(sql);
    }
    public void createDatabase(String dataBase,String collation,String charset) throws SQLException{
        Statement statement = connection.createStatement();
        //CREATE DATABASE db_name CHARACTER charset_name SET COLLATE collation_name
        String sql = "CREATE DATABASE "+dataBase + " CHARACTER "+charset+" SET COLLATE "+collation;
        statement.executeUpdate(sql);
    }
    public void deleteDatabases(String [] dataBases) throws SQLException{
        Statement statement = connection.createStatement();
        //DROP DATABASE IF EXIST db_name
        for(int i = 0; i < dataBases.length;i++){
            System.out.println("delete"+dataBases[i]);
            String sql = "DROP DATABASE "+dataBases[i];
            statement.executeUpdate(sql);
        }
    }
    public ResultSet viewDatabases() throws SQLException{
        Statement statement = connection.createStatement();
        //DROP DATABASE IF EXIST db_name
        String sql = "SHOW DATABASES";
        return statement.executeQuery(sql);
    }
    public ResultSet collation() throws SQLException{
        System.out.println("connection"+connection.toString());
        Statement statement = connection.createStatement();
        String sql = "SHOW CHARACTER SET";
        return statement.executeQuery(sql);
    }
    public ResultSet databaseTables(String database) throws SQLException{
        String sql = "SHOW TABLES FROM "+database;
        return connection.createStatement().executeQuery(sql);
    }
    public ResultSet databaseProcedures(String database) throws SQLException{
        String sql = "show procedure status where Db = \'"+database+"\'";
        return connection.createStatement().executeQuery(sql);
    }
    public ResultSet databaseTriggers(String database) throws SQLException{
        String sql = "show triggers from "+database;
        return connection.createStatement().executeQuery(sql);
    }
    public ResultSet databaseFunctions(String database) throws SQLException{
        String sql = "show function status where Db = \'"+database+"\'";;
        return connection.createStatement().executeQuery(sql);
    }
    public ResultSet tableIndex(String table, String database) throws SQLException{
        String sql = "show index from "+ table + " from " + database;
        return connection.createStatement().executeQuery(sql);
    }
}
